import os, sys
import openpyxl
from Tools.tools import INIReader, LOG
from openpyxl.styles import Alignment,Border,Side,colors
sys.path.append(os.path.dirname(sys.path[0]))


class saveExcel:

    def __init__(self, file=INIReader('EXCEL','excelpath')):
        self.file = file
        if  self.file  in os.listdir(os.getcwd()):
            os.remove(self.file )
            LOG().WARNING('旧案例删除成功！')
        else:
            pass
        self.book = openpyxl.Workbook()
        self.sheet = self.book.active
        self.sheet.title = '何庭彦测试案例'
        self.sheet['A1'] = '案例编号'
        self.sheet['B1'] = '测试模块'
        self.sheet['C1'] = '测试点'
        self.sheet['D1'] = '测试内容'
        self.sheet['E1'] = '场景描述'
        self.sheet['F1'] = '前置条件'
        self.sheet['G1'] = '操作步骤'
        self.sheet['H1'] = '测试数据'
        self.sheet['I1'] = '预期结果'
        self.sheet['J1'] = '测试结果'
        self.sheet['K1'] = '测试人员'
        self.sheet['L1'] = '备注'
        self.sheet.column_dimensions['C'].width = 24
        self.sheet.column_dimensions['D'].width = 38
        self.sheet.column_dimensions['F'].width = 38
        self.sheet.column_dimensions['G'].width = 40
        self.sheet.column_dimensions['I'].width = 24
        for i in [chr(x) for x in range(ord('A'), ord('L') + 1)]:
            self.sheet[i+'1'].font = openpyxl.styles.Font('微软雅黑',size = 11,bold=True)
            self.sheet[i+'1'].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
            self.sheet[i+'1'].fill = openpyxl.styles.PatternFill("solid",fgColor='DCDCDC')
        self.sheet.row_dimensions[1].height = 20

    def __del__(self):
        # for i in [chr(x) for x in range(ord(''), ord('L') + 1)]:
        #     for n in range(self.sheet.max_row+1):
        #         self.sheet[i+str(n)].alignment = openpyxl.styles.Alignment(horizontal='center', vertical='center')
        self.book.save(self.file )

    def center_center(self, cell:str):
        align = Alignment(horizontal='center', vertical='center', wrap_text=True)
        self.sheet[cell].alignment =  align

    def left_top(self,cell:str):
        align = Alignment(horizontal='left', vertical='top', wrap_text=True)
        self.sheet[cell].alignment =  align

    def left_center(self,cell:str):
        align = Alignment(horizontal='left', vertical='center', wrap_text=True)
        self.sheet[cell].alignment =  align

    def my_border(self, t_border, b_border, l_border, r_border):
        border = Border(top=Side(border_style=t_border, color=colors.BLACK),
                    bottom=Side(border_style=b_border, color=colors.BLACK),
                    left=Side(border_style=l_border, color=colors.BLACK),
                    right=Side(border_style=r_border, color=colors.BLACK))
        return border

    def format_border(self, s_column, s_index, e_column , e_index):
        for row in tuple(self.sheet[s_column + str(s_index):e_column + str(e_index)]):
            for cell in row:
                cell.border = self.my_border('thin', 'thin', 'thin', 'thin')

    def intoOne_B(self,start:int, end:int):
        self.sheet.merge_cells('B{}:B{}'.format(start, end))

    def intoOne_C(self,start:int, end:int):
        self.sheet.merge_cells('C{}:C{}'.format(start, end))

    def writerow(self, list):
        self.sheet.append(list)

    def getValue(self, cellCone):
        return self.sheet[cellCone].value

if __name__ == '__main__':
    saveExcel()